﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//using System.Threading.Tasks;
//using System.Data.SqlClient;
using System.ServiceModel;
using System.Runtime.Serialization;
using Database;
using System.IO;
using System.Data;
using System.Data.SqlServerCe;

namespace DataBase
{
    [DataContract]
    public class SQLInit
    {
      //  string path = @"Data Source=(LocalDB)\v11.0; AttachDbFilename= " + Directory.GetParent(Directory.GetCurrentDirectory()).Parent.Parent.FullName + "\\Database\\RealDatabase.mdf ;Integrated Security=True";
   //     string path = @"Data Source=C:\Users\user\Desktop\Forum_V3\Database\Database.sdf";
        string path = "Data Source=" + Directory.GetParent(Directory.GetCurrentDirectory()).Parent.Parent.FullName + "\\Database\\Database.sdf";
   
        //        SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\v11.0; AttachDbFilename= C:\Users\user\Desktop\semesterB\sadna\סדנא מעודכן\Forum_v2\WCFService\ClassLibrary1\DataBaseNew\RealDatabase.mdf ;Integrated Security=True");
    
         
        //registerUser
        public void registerUser(int forumId, string uname, string name, string lastName, string password, string email)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c; 
            c.Open();
            cmd.CommandText = "INSERT INTO [User] (uname, name, lastName, password, email, forumId) " +
                        "Values ('" + uname + "', '" + name + "', '" + lastName + "', '" + password + "', '" + email + "', " + forumId + ")";
                      try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException e) { }
      //      cmd.Clone();
            c.Close();
        }
        
        //admin ->will be the manager of the forum.
        //  int addForum(string title, User admin);
        public void addForum(string title, int forumId, string uname, string name, string lastName, string password, string email)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //create forum
            cmd.CommandText = "INSERT INTO [Forum] (id, title, admin, idForSubForums) " +
                        "Values (" + forumId + ", '" + title + "', '" + uname + "', 0)";
            try 
            { 
                cmd.ExecuteNonQuery(); 
            }
            catch (SqlCeException e) { }
          //  cmd.Clone();
            connection.Close();
            //register director to the forum
            registerUser(forumId, "Gilad", "Gilad", "Sabari", "123", "bla@bla.com");
            //register admin to the forum
            registerUser(forumId, uname, name, lastName, password, email);
            updateDBIdforForums(forumId);

        }

        //addSubForum(int forumId, string title);
        public void addSubForum(int id, string title, int forumId, string uname) {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //create subforum
            cmd.CommandText = "INSERT INTO [SubForum] (id, title, forumId, idForThreads) " +
                        "Values (" + id + ", '" + title + "', " + forumId + ", 0)";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException e) { }
            //cmd.Clone();
            connection.Close();
            //add moderator uname -(admin)
            addSubForumModerator(forumId, id, uname);
            //update idforSubForums in Forum +1
            updateFIdforSubForums(forumId, id);
            
        }

        //addSubForumModerator(int forumId, int subForumId, string uname);
        public void addSubForumModerator(int forumId, int subForumId, string uname)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //create subforum
            cmd.CommandText = "INSERT INTO [Moderator] (uname, forumId, subForumId, complaint) " +
                        "Values ('" + uname + "', " + forumId + ", " + subForumId + ", 0)";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException e) { }
            //cmd.Clone();
            connection.Close();
        }
        
        //addThread(int forumId, int subForumId, string title, string body, User user);
        public void addThread(int id, int forumId, int subForumId, string title, string body, string uname)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //create thread
            cmd.CommandText = "INSERT INTO [Thread] (id, title, body, creationTime, [user], forumId, subForumId, idForPosts) " +
                        "Values (" + id + " , '" + title + "', '" + body + "', '" + DateTime.Now + "', '" + uname + "', " + forumId + ", " + subForumId + ", 0)";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException e) { }
        //    cmd.Clone();
            connection.Close();
            //update idForThreads in subForum +1
            updateSFidForThreads(forumId, subForumId, id);
        }

        //int addPost(int forumId, int subForumId, int threadId, string title, string body, User user);
        public void addPost(int id, int forumId, int subForumId, int threadId, string title, string body, string uname)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //create subforum
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "INSERT [Post] (id, title, body, creationTime, [user], forumId, subForumId, ThreadId) " +
                        "VALUES (" + id + ", '" + title + "', '" + body + "', '" + DateTime.Now.ToString() + "', '" + uname + "', " + forumId + ", " + subForumId + ", " + threadId + ")";
            try
            {
                cmd.ExecuteNonQuery();
            }
           catch (SqlCeException e) {  }
        //    cmd.Clone();  
            connection.Close();
            
            //TODO:: update idForPosts in thread +1
            updateTIdForPosts(forumId, subForumId, threadId, id);
          
        }

        //addDataBase(int id, string director)
        public void addDataBase(int id, string director)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //create subforum
            cmd.CommandText = "INSERT INTO [DataBase] (id, director, idforForums) " +
                        "Values (" + id + ", '" + director + "', 0)";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException e) { }
          //  cmd.Clone();
            connection.Close();
        }

        //addDataBase(int id, string director)
        public void addFriend(int forumId, string username, string friend)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //add friend
            cmd.CommandText = "INSERT INTO [UserFriends] (forimId, uname, friend) " +
                        "Values (" + forumId + ", '" + username + "', '" + friend + "')";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException e) { }
        //    cmd.Clone();
            connection.Close();
        }

///////////////////////////       Update Data     ///////////////////////////////////////////////////
        
        //update Thread Body
        public void updateThreadBody(int forum_id, int sub_forum_id, int threadId, string body)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update thread title
            cmd.CommandText = "UPDATE [Thread] set body = '" + body + "' WHERE id = " + threadId + "and forumId = " + forum_id + "and subForumId = " + sub_forum_id;
            cmd.ExecuteNonQuery();
        //    cmd.Clone();
            c.Close();
        }

        //update Thread Title
        public void updateThreadTitle(int forum_id, int sub_forum_id, int threadId, string title)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update thread title
            cmd.CommandText = "UPDATE [Thread] set title = '" + title + "' WHERE id = " + threadId + "and forumId = " + forum_id + "and subForumId = " + sub_forum_id;
            cmd.ExecuteNonQuery();
         //   cmd.Clone();
            c.Close();
        }
        //setSubForumTitle
        public void updateSubForumTitle(int forum_id, int subForumId, string title)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update forum title
            cmd.CommandText = "UPDATE [SubForum] set title = '" + title + "' WHERE id = " + subForumId + "and forumId = " + forum_id;
            cmd.ExecuteNonQuery();
         //   cmd.Clone();
            c.Close();
        }
        
        //setForumTitle
        public void updateForumTitle(int forumId, string title)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update forum title
            cmd.CommandText = "UPDATE  [Forum] set title = '" + title + "' WHERE id = " + forumId;
            cmd.ExecuteNonQuery();
            //cmd.Clone();
            c.Close();
        }
        
        public void updateDBIdforForums(int num)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //update idforForums
            cmd.CommandText = "UPDATE  [DataBase] set idforForums = " + num + "WHERE id = 1" ;
            cmd.ExecuteNonQuery();
           // cmd.Clone();
            connection.Close();
        }

        public void updateFIdforSubForums(int id, int num)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //update idforForums
            cmd.CommandText = "UPDATE [Forum] set idForSubForums = " + num + "WHERE id = "+ id;
            cmd.ExecuteNonQuery();
          //  cmd.Clone();
            connection.Close();
        }

        public void updateSFidForThreads(int ForumId, int subForumId, int num)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //update idforForums
            cmd.CommandText = "UPDATE [SubForum] set idForThreads = " + num + "WHERE id = " + subForumId + "and forumId = " + ForumId;
            cmd.ExecuteNonQuery();
            //cmd.Clone();
            connection.Close();
        }

        public void updateTIdForPosts(int ForumId, int SubForumId, int threadId, int num)
        {
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = connection;
            connection.Open();
            //update idforForums
            cmd.CommandText = "UPDATE [Thread] set idForPosts = " + num + "WHERE id = " + threadId + "and forumId = " + ForumId + "and subForumId = " + SubForumId;
            cmd.ExecuteNonQuery();
            //cmd.Clone();
            connection.Close();
        }

        /*
        public void updateForumAdmin()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connection;
            connection.Open();
            //update idforForums
            cmd.CommandText = "UPDATE [Forum] set admin = 'Ilya' WHERE id = " + 1;
            cmd.ExecuteNonQuery();
            cmd.Clone();
            connection.Close();
        }
        */

        //setPostTitle
        public void updatePostTitle(int forumId, int subForumId, int threadId, int postId, string title)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update forum title
            cmd.CommandText = "UPDATE  [Post] set title = '" + title + "' WHERE id = " + postId +
                "and forumId = " + forumId + "and subForumId = " + subForumId + "and ThreadId = " + threadId;
            cmd.ExecuteNonQuery();
            //cmd.Clone();
            c.Close();
        }
        //setPostBody
        public void updatePostBody(int forumId, int subForumId, int threadId, int postId, string body)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update forum title
            cmd.CommandText = "UPDATE  [Post] set body = '" + body + "' WHERE id = " + postId +
                "and forumId = " + forumId + "and subForumId = " + subForumId + "and ThreadId = " + threadId;
            cmd.ExecuteNonQuery();
            //cmd.Clone();
            c.Close();
        }

        //setModeratorComplaints
        public void updateModeratorComplaints(int forumId, int subForumId, string moderator, int complaint)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            //update forum title
            cmd.CommandText = "UPDATE  [Moderator] set complaint = '" + complaint + "' WHERE forumId = " + forumId +
                "and subForumId = " + subForumId + "and uname = '" + moderator + "'";
            cmd.ExecuteNonQuery();
           // cmd.Clone();
            c.Close();
        }


/// /////////////////////////////////////       Read Data         ///////////////////////////



        public DBmediator initDataBase(DBmediator db){
      
       //     Console.WriteLine("Trying to connect to database");
   //         Console.WriteLine(path);
            SqlCeConnection connection = new SqlCeConnection(path);
            SqlCeDataReader dr1;
            SqlCeCommand cmd = new SqlCeCommand();    
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM Forum";
            cmd.Connection = connection;
            connection.Open();
            dr1 = cmd.ExecuteReader();
         //   Console.WriteLine("Connected to database!");
            //add forums
            while (dr1.Read()) {
            //    Console.WriteLine("111");
                User admin = getUser((string)dr1["admin"], (int)dr1["id"]);
                db.setIdForForums((int)dr1["id"] - 1);
                db.addForum((string)dr1["title"], admin);
                
            }
            dr1.Close();
            //cmd.Clone();
   //         Console.WriteLine("Connected to database!");
            //add users
            cmd.CommandText = "SELECT * from [User]";
            dr1 = cmd.ExecuteReader();
            while (dr1.Read())
            {
                int forumId = (int)dr1["forumId"];
                string uname = (string)dr1["uname"];
                string name = (string)dr1["name"];
                string lastName = (string)dr1["lastName"];
                string password = (string)dr1["password"];
                string email = (string)dr1["email"];

                if (!uname.Equals(db.getForumAdmin(forumId).getUserName()))
                    db.registerUser(forumId, uname, name, lastName, password, email);
            //    cmd.Clone();
            }
            dr1.Close();

            //add subforums
            cmd.CommandText = "select * from [SubForum]";
            dr1 = cmd.ExecuteReader();
            while (dr1.Read()) {
            int f_id = (int)dr1["forumId"];
            int sf_id = (int)dr1["id"];
            string title = (string)dr1["title"];
            db.setIdForSubForums(f_id, sf_id - 1);
            db.addSubForum(f_id, title);

          //  cmd.Clone();
            }
            dr1.Close();

            // add moderators
            cmd.CommandText = "select * from [Moderator]";
            dr1 = cmd.ExecuteReader();
            while (dr1.Read())
            {
                string uname = (string)dr1["uname"];
                int forumId = (int)dr1["forumId"];
                int subForumId = (int)dr1["subForumId"];
                int complaint = (int)dr1["complaint"];           
                User user = getUser(uname, forumId);
                if (!db.isSubForumModerator(forumId, subForumId, user))
                    db.addSubForumModerator(forumId, subForumId, uname);
                db.setModeratorComplaints(forumId, subForumId, uname, complaint);
     //           cmd.Clone();
            }
            dr1.Close();

            //add threads
            cmd.CommandText = "select * from [Thread]";
            dr1 = cmd.ExecuteReader();
            while (dr1.Read())
            {
                int thread_id = (int)dr1["id"];
                string title = (string)dr1["title"];
                string body = (string)dr1["body"];
                DateTime creationTime = Convert.ToDateTime((string)dr1["creationTime"]);
                string uname = (string)dr1["user"];
                int forumId = (int)dr1["forumId"];
                int subForumId = (int)dr1["subForumId"];
                db.setIdForThreads(forumId, subForumId, thread_id - 1);
                User user = getUser(uname, forumId);
                db.addThread(forumId, subForumId, title, body, user);
       //         cmd.Clone();
            }
            dr1.Close();
            
            //add post
            cmd.CommandText = "select * from [Post]";
            dr1 = cmd.ExecuteReader();
            while (dr1.Read())
            {
                int postId = (int)dr1["id"];
                string title = (string)dr1["title"];
                string body = (string)dr1["body"];
                DateTime creationTime = Convert.ToDateTime((string)dr1["creationTime"]);
                string uname = (string)dr1["user"];
                int forumId = (int)dr1["forumId"];
                int subForumId = (int)dr1["subForumId"];
                int thread_id = (int)dr1["ThreadId"];
                User user = getUser(uname, forumId);
                db.setIdForPosts(forumId, subForumId, thread_id, postId - 1);
                db.addPost(forumId, subForumId, thread_id, title, body, user);
           //     cmd.Clone();
            }
            dr1.Close();
            

            //add friends
            cmd.CommandText = "select * from [UserFriends]";
            dr1 = cmd.ExecuteReader();
            while (dr1.Read())
            {
                int f_id = (int)dr1["forimId"];
                string uname = (string)dr1["uname"];
                string friend = (string)dr1["friend"];
                db.addFriend(f_id, uname, friend);
       //         cmd.Clone();
            }
            dr1.Close();

            connection.Close();
            return db;

       }

        public User getUser(string name, int forum_id) {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeDataReader dr;
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c; 
            c.Open();
            cmd.CommandText = "select * from [User] WHERE (uname = '" + name + "' and forumId = " + forum_id + ")";
            dr = cmd.ExecuteReader();
            dr.Read();
            User user = new User((string)dr["uname"],(string)dr["name"], (string)dr["lastName"], (string)dr["password"], (string)dr["email"]);            
            //TODO:: add friends
           // cmd.Clone();
            c.Close();
            return user;
        }
        

///////////////////////////        Delete        //////////////////////////
        public void removeSubForumModerator(int forum_id, int sub_forum_id, string u_name)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            cmd.CommandText = "delete from [Moderator] WHERE forumId = " + forum_id + " and subForumId = " + sub_forum_id + " and uname = '" + u_name + "'"; 
            cmd.ExecuteReader();   
           // cmd.Clone();
            c.Close();
        }

        public void removeThread(int forum_id, int sub_forum_id, int thread_id)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            SqlCeDataReader dr;
            cmd.Connection = c;
            c.Open();
            //remove thread
            cmd.CommandText = "delete from [Thread] WHERE id = " + thread_id + " and forumId = " + forum_id + " and subForumId = " + sub_forum_id;
            dr = cmd.ExecuteReader();
            //cmd.Clone();
            dr.Close();
            // remove posts  
            cmd.CommandText = "delete from [Post] WHERE forumId = " + forum_id + " and subForumId = " + sub_forum_id + " and threadId = " + thread_id;
            cmd.ExecuteReader();
            //cmd.Clone();
            dr.Close();
            c.Close();
            
        }
        
        public void removePost(int forum_id, int sub_forum_id, int thread_id, int post_id)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            // remove posts
            cmd.CommandText = "delete from [Post] WHERE id = " + post_id + "and forumId = " + forum_id + " and subForumId = " + sub_forum_id + " and threadId = " + thread_id;
            cmd.ExecuteReader();
           // cmd.Clone();
            c.Close();
        }

        public void removeFriend(int forumId, string username, string friend)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            // remove friend
            cmd.CommandText = "delete from [UserFriends] WHERE forimId = " + forumId + "and uname = '" + username + "' and friend = '" + friend + "'";
            cmd.ExecuteReader();
          //  cmd.Clone();
            c.Close();
        }

        public void removeModerator(int forumId, int subForumId, string m_name)
        {
            SqlCeConnection c = new SqlCeConnection(path);
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = c;
            c.Open();
            // remove friend
            cmd.CommandText = "delete from [UserFriends] WHERE forumId = " + forumId + "and subForumId = " + subForumId + " and uname = '" + m_name + "'";
            cmd.ExecuteReader();
        //    cmd.Clone();
            c.Close();
        }
    }
}
